
***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


***************************************
*** BASE DATA 
***************************************

tempfile temp_base0
save  `temp_base0' , replace
	
*drop observations with emtpy prices 
keep if bidprice_bl!=. &  askprice_bl!=. & midprice_bl!=.

*round to x decimals
foreach var of varlist bidprice_bl askprice_bl midprice_bl{
	gen double a1=`var'*100000
	gen double a0 = round(a1, 1)
	replace `var' = a0 /100000
	drop a1 a0
}


tempfile temp_base
save  `temp_base' , replace



*********************************
*** EXCEL SHEET WITH YAS YIELDS 
*********************************

foreach y of numlist 2016 2017{
	foreach i of numlist 1/2{

	import excel "`dir'/2 YAS/BL_prices`y'.xlsx", sheet("`y'_`i'") firstrow clear

	rename YAS_bidprice_bl bidYAS_bl
	rename YAS_askprice_bl askYAS_bl 
	rename YAS_midprice_bl midYAS_bl 

	destring bidYAS_bl, replace force 
	destring askYAS_bl, replace force 
	destring midYAS_bl, replace force 
		
	tempfile temp_`y'_`i'
	save temp_`y'_`i' , replace
	
	}
}


foreach y of numlist 2018 2019{
	foreach i of numlist 1/2{

	import excel "`dir'/2 YAS/BL_prices20182019.xlsx", sheet("`y'_`i'") firstrow clear

	rename YAS_bidprice_bl bidYAS_bl
	rename YAS_askprice_bl askYAS_bl 
	rename YAS_midprice_bl midYAS_bl 

	destring bidYAS_bl, replace force 
	destring askYAS_bl, replace force 
	destring midYAS_bl, replace force 
	
		
	tempfile temp_`y'_`i'
	save temp_`y'_`i' , replace
	
	}
}


use temp_2016_1 , clear

	foreach y of numlist 2016 {
		foreach i of numlist 2{
		append using temp_`y'_`i'
		
		}
	}
	
	foreach y of numlist 2017 2018 2019 {
		foreach i of numlist 1/2{
		append using temp_`y'_`i'
		}
	}
	

format settlement_date %td

*destring and rename  
keep isin settlement_date bidprice_bl askprice_bl midprice_bl  bidYAS_bl askYAS_bl midYAS_bl
sort isin settlement_date bidprice_bl askprice_bl midprice_bl

*round to x decimals
foreach var of varlist bidprice_bl askprice_bl midprice_bl{
	gen double a1=`var'*100000
	gen double a0 = round(a1, 1)
	replace `var' = a0 /100000
	drop a1 a0
}

*dop duplicates
duplicates drop 
bys isin settlement_date bidprice_bl askprice_bl midprice_bl: gen a0=_N
bys isin settlement_date bidprice_bl askprice_bl midprice_bl: gen a1=_n
browse if a0>1 
keep if a0==1 | (a0>1 & a1==1)


***************************************
*** MERGE WITH BASE DATA AND CLEAN UP
***************************************

keep isin settlement_date  bidprice_bl askprice_bl midprice_bl bidYAS_bl askYAS_bl midYAS_bl
sort isin settlement_date bidprice_bl askprice_bl midprice_bl

*merge with base data 		
merge 1:m isin settlement_date  bidprice_bl askprice_bl midprice_bl using `temp_base'

drop if _merge==1 // those are observations in YAS that are not in my data
rename  _merge _merge_yas_bl

tempfile tempbase2
save  `tempbase2' , replace
	

	*** Separate the observatiosn that did not match
	keep if _merge_yas_bl==2
	count
	sort isin settlement_date  bidprice_bl askprice_bl midprice_bl 
	browse 

	tempfile tempadd3
	save  `tempadd3' , replace

	duplicates drop isin settlement_date  bidprice_bl askprice_bl midprice_bl , force

	*** Import the excel sheet with the missing YAS yields for observations that did not match		
	import excel "`dir'/2 YAS/missingBL2.xlsx", sheet("Sheet1") firstrow clear

	*** Merge with the rest
	merge 1:m  isin  settlement_date bidprice_bl askprice_bl midprice_bl  using `tempadd3'
	rename _merge _merge_round2_bl
	
	tempfile tempbase4
	save  `tempbase4' , replace
	
	use `tempbase2' , clear 
	drop if _merge_yas_bl==2
	append using `tempbase4'


*put into basis points
replace bidYAS_bl = bidYAS_bl*100
replace askYAS_bl = askYAS_bl*100
replace midYAS_bl = midYAS_bl*100

tempfile time_final
save  `time_final' , replace
	
*bring back the observations that I droped above	
use  `temp_base0' , clear 

keep if bidprice_bl==. |  askprice_bl==. | midprice_bl==.
append using `time_final'
count 


	
